package cn.yangliu.jpa.handler;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import javax.sql.DataSource;

import cn.yangliu.jpa.annotations.sql.JpaSqlMapping;
import cn.yangliu.jpa.annotations.sql.JpaSqlQuery;
import cn.yangliu.jpa.enums.DbTypeEnum;
import cn.yangliu.jpa.enums.LinkTypeEnum;
import cn.yangliu.jpa.enums.QueryTypeEnum;
import cn.yangliu.jpa.function.ParamTransformer;
import cn.yangliu.jpa.function.ValueTransformer;
import cn.yangliu.jpa.tools.JpaUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;

/**
 * The interface Param transformer.
 *
 * @author 问道于盲
 * @date 2019-06-14
 */
class SqlHandler {

    /**
     * current class's static member
     * The constant LOGGER.
     */
    private static final Logger LOGGER = LoggerFactory.getLogger(SqlHandler.class);

    /**
     * Instantiates a new Sql handler.
     * forbid other class to create a new instance.
     * only itself can create a new instance.
     */
    private SqlHandler() {
    }


    /**
     * 通过动态sql 实现复杂查询并映射
     *
     * @param <T>              泛型
     * @param dataSource       数据源
     * @param queryPojo        查询条件封装
     * @param paramTransformer 参数转换器
     * @param valueTransformer 结果转换器
     * @return 单条数据 t
     */
    static <T> T queryForObjectBySql(DataSource dataSource, Object queryPojo,
                                     ParamTransformer paramTransformer, ValueTransformer valueTransformer) {
        Connection connection = null;
        PreparedStatement queryPs = null;
        PreparedStatement countPs = null;
        ResultSet resultSet = null;

        Class<T> resultClass = JpaUtils.getResultClass(queryPojo);

        try {
            connection = dataSource.getConnection();

            StringBuilder sql = new StringBuilder();
            List<Object> params = new ArrayList<>();
            buidSql(queryPojo, null, sql, null, params, paramTransformer);

			queryPs = connection.prepareStatement(sql.toString());
			countPs = connection.prepareStatement(sql.toString());
            doLog(params, sql.toString());

            long count = getCount(countPs, params);

            if (count > 1L) {
				throw new UnsupportedOperationException("more than one result found");
			}
            resultSet = getResultSet(queryPs, params);
            T data = resultClass.newInstance();
            analysisResultSet(resultSet, resultClass, data, valueTransformer);
            JpaUtils.THREAD_LOCAL.remove();
            return data;
        } catch (Exception e) {
            throw new UnsupportedOperationException(e.getMessage(), e);
        } finally {
            close(resultSet);
            close(countPs);
            close(queryPs);
            close(connection);
        }

    }

    /**
     * Close.
     *
     * @param closeable the closeable
     */
    public static void close(AutoCloseable closeable) {
        if (Objects.isNull(closeable)) {
            return;
        }
        try {
            closeable.close();
        } catch (Exception e) {
            LOGGER.debug(e.getMessage(), e);
        }
    }

    /**
     * 构建sql
     *
     * @param queryPojo        查询条件封装
     * @param pageable         分页信息
     * @param querySql         查询sql
     * @param countSql         统计sql
     * @param params           参数
     * @param paramTransformer 参数转换器
     */
    private static void buidSql(Object queryPojo, Pageable pageable, StringBuilder querySql,
                                StringBuilder countSql, List<Object> params, ParamTransformer paramTransformer) {
        String sourceSql = JpaUtils.getSourceSql(queryPojo);

        boolean doPage = Objects.nonNull(pageable);
        doPageStart(sourceSql, querySql, countSql, doPage);

        querySql.append(sourceSql);
        if (Objects.isNull(queryPojo)) {
            doPageEnd(pageable, querySql, countSql, doPage);
            return;
        }

        List<String> orders = new ArrayList<>();
        List<String> directions = new ArrayList<>();
        boolean sorted = JpaUtils.isSorted(pageable, orders, directions);

        buildConditions(queryPojo, querySql, countSql, params, paramTransformer, orders);

        String orderBy = JpaUtils.buildOrderBy(sorted, orders, directions);
        querySql.append(orderBy);
        if (doPage) {
            countSql.append(orderBy);
        }

        doPageEnd(pageable, querySql, countSql, doPage);
    }


    /**
     * 构建查询条件
     *
     * @param queryPojo        参数封装
     * @param querySql         查询sql
     * @param countSql         统计sql
     * @param params           参数集合
     * @param paramTransformer 参数转换器
     * @param sortFields       the sort fields
     */
    private static void buildConditions(Object queryPojo, StringBuilder querySql, StringBuilder countSql,
                                        List<Object> params, ParamTransformer paramTransformer,
                                        List<String> sortFields) {
        List<Field> fieldList = JpaUtils.getDeclaredFields(queryPojo);
        fieldList = JpaUtils.sort(fieldList, JpaSqlQuery.class);
        boolean whereFlag = false;
        for (Field declaredField : fieldList) {

            Object value = JpaUtils.getFieldValue(queryPojo, declaredField);

            if (Objects.isNull(value) || Objects.equals(value, "")) {
                continue;
            }

            if (value.getClass().isArray() && ((Object[]) value).length == 0) {
                continue;
            }
            if (((value instanceof Collection) && ((Collection<?>) value).isEmpty())) {
                continue;
            }

            JpaSqlQuery jpaSqlQuery = declaredField.getDeclaredAnnotation(JpaSqlQuery.class);


            if (Objects.isNull(jpaSqlQuery)) {
                continue;
            }

            QueryTypeEnum queryTypeEnum = jpaSqlQuery.type();
            if (Objects.equals(QueryTypeEnum.LIKE, queryTypeEnum)) {
                value = "%" + value.toString() + "%";
            }
            if (Objects.equals(QueryTypeEnum.STARTWITH, queryTypeEnum)) {
                value = value.toString() + "%";
            }
            if (Objects.equals(QueryTypeEnum.ENDWITH, queryTypeEnum)) {
                value = "%" + value.toString();
            }
            String[] columns = getColumns(declaredField, jpaSqlQuery);
            if (columns.length == 1) {
                String column = columns[0];
                buildCondition(querySql, countSql, params, paramTransformer, sortFields, whereFlag,
                        declaredField, jpaSqlQuery, column, value);
                whereFlag = true;
                continue;
            }

            buildInnerCondition(querySql, countSql, params, paramTransformer, sortFields, whereFlag,
                    declaredField, jpaSqlQuery, columns, value);

            whereFlag = true;
        }
    }

    /**
     * Build condition.
     *
     * @param querySql         the query sql
     * @param countSql         the count sql
     * @param params           the params
     * @param paramTransformer the param transformer
     * @param sortFields       the sort fields
     * @param whereFlag        the where flag
     * @param declaredField    the declared field
     * @param jpaSqlQuery      the jpa sql query
     * @param column           the column
     * @param value            the value
     */
    private static void buildCondition(StringBuilder querySql, StringBuilder countSql, List<Object> params,
                                       ParamTransformer paramTransformer, List<String> sortFields,
                                       boolean whereFlag, Field declaredField, JpaSqlQuery jpaSqlQuery, String column,
                                       Object value) {
        String fieldName = declaredField.getName();

        if (sortFields.contains(fieldName)) {
            int index = sortFields.indexOf(fieldName);
            sortFields.remove(fieldName);
            sortFields.add(index, column);
        }
        LinkTypeEnum linkType = jpaSqlQuery.link();
        QueryTypeEnum queryTypeEnum = jpaSqlQuery.type();

        value = paramTransformer.transform(column, declaredField.getName(), value);

        appendWhere(querySql, countSql, whereFlag);

        querySql.append(JpaUtils.linkType(linkType, whereFlag));
        countSql.append(JpaUtils.linkType(linkType, whereFlag));
        if (JpaUtils.isNotEmptyArray(value)) {
            querySql.append(column).append(" ").append(queryTypeEnum.symbol());
            JpaUtils.buildArrayParams(querySql, value);
            countSql.append(column).append(" ").append(queryTypeEnum.symbol());
            JpaUtils.buildArrayParams(countSql, value);
        } else {
            params.add(value);
            querySql.append(column).append(" ").append(queryTypeEnum.symbol()).append(" ").append("?");
            countSql.append(column).append(" ").append(queryTypeEnum.symbol()).append(" ").append("?");
        }


    }

    /**
     * 构建内联查询条件
     * 如(id in (1,2,3) and parent_id in (1,2,3))
     *
     * @param querySql         the query sql
     * @param countSql         the count sql
     * @param params           the params
     * @param paramTransformer the param transformer
     * @param sortFields       the sort fields
     * @param whereFlag        the where flag
     * @param declaredField    the declared field
     * @param jpaSqlQuery      the jpa sql query
     * @param columns          the columns
     * @param value            the value
     */
    private static void buildInnerCondition(StringBuilder querySql, StringBuilder countSql, List<Object> params,
                                            ParamTransformer paramTransformer, List<String> sortFields,
                                            boolean whereFlag, Field declaredField, JpaSqlQuery jpaSqlQuery,
                                            String[] columns,
                                            Object value) {

        String fieldName = declaredField.getName();
        QueryTypeEnum queryTypeEnum = jpaSqlQuery.type();
        appendWhere(querySql, countSql, whereFlag);

        LinkTypeEnum linkType = jpaSqlQuery.link();
        if (whereFlag) {
            querySql.append(linkType.name());
            countSql.append(linkType.name());
        }
        querySql.append(" (");
        countSql.append(" (");
        boolean start = true;
        for (String column : columns) {

            if (sortFields.contains(fieldName)) {
                int index = sortFields.indexOf(fieldName);
                sortFields.remove(fieldName);
                sortFields.add(index, column);
            }
            LinkTypeEnum innerLink = jpaSqlQuery.innerLink();

            value = paramTransformer.transform(column, declaredField.getName(), value);

            if (start) {
                start = false;
                querySql.append(" ");
                countSql.append(" ");
            } else {
                querySql.append(JpaUtils.linkType(innerLink, true));
                countSql.append(JpaUtils.linkType(innerLink, true));
            }

            if (JpaUtils.isNotEmptyArray(value)) {
                querySql.append(column).append(" ").append(queryTypeEnum.symbol()).append(" ");
                countSql.append(column).append(" ").append(queryTypeEnum.symbol()).append(" ");
                JpaUtils.buildArrayParams(querySql, value);
                JpaUtils.buildArrayParams(countSql, value);
            } else {
                querySql.append(column).append(" ").append(queryTypeEnum.symbol()).append(" ").append("?");
                countSql.append(column).append(" ").append(queryTypeEnum.symbol()).append(" ").append("?");
                params.add(value);
            }
        }
        querySql.append(")");
        countSql.append(")");
    }

    /**
     * 添加where
     *
     * @param querySql  the query sql
     * @param countSql  the count sql
     * @param whereFlag the where flag
     */
    private static void appendWhere(StringBuilder querySql, StringBuilder countSql, boolean whereFlag) {
        if (!whereFlag) {
            querySql.append(JpaUtils.WHERE);
            countSql.append(JpaUtils.WHERE);
        }
    }

    /**
     * 获取字段
     *
     * @param declaredField 属性
     * @param jpaSqlQuery   属性上的JpaSqlQuery注解
     * @return SQL语句中的字段 column
     */
    private static String[] getColumns(Field declaredField, JpaSqlQuery jpaSqlQuery) {
        String[] columns = jpaSqlQuery.columns();
        if (columns.length == 0) {
            return new String[]{JpaUtils.humpToUnderline(declaredField.getName()).toUpperCase()};
        }
        return columns;
    }

    /**
     * 分页的前头语句
     *
     * @param sourceSql 原sql
     * @param querySql  查询sql
     * @param countSql  统计sql
     * @param doPage    是否分页
     */
    private static void doPageStart(String sourceSql, StringBuilder querySql, StringBuilder countSql, boolean doPage) {
        if (!doPage) {
            return;
        }
        if (Objects.equals(DbTypeEnum.ORACLE, JpaUtils.getDbType())) {
            querySql.append("select * from (select ROWNUM rn,TEMPTABLE.* FROM (");
        }
        countSql.append("select COUNT(*) COUNT from (");
        countSql.append(sourceSql);
    }

    /**
     * 判断是不是分页查询
     *
     * @param pageable 分页参数
     * @param querySql 查询sql
     * @param countSql 统计sql
     * @param doPage   是否做分页
     */
    private static void doPageEnd(Pageable pageable, StringBuilder querySql, StringBuilder countSql, boolean doPage) {
        if (!doPage) {
            return;
        }
        if (Objects.equals(DbTypeEnum.ORACLE, JpaUtils.getDbType())) {
            querySql.append(") TEMPTABLE ) where rn > ");
            querySql.append(pageable.getOffset()).append(" and ").append("rn <= ")
                    .append((pageable.getPageNumber() + pageable.getOffset()));
            countSql.append(")");
        } else {
            querySql.append(" limit ").append(pageable.getOffset()).append(",").append(pageable.getPageSize());
            countSql.append(") as temptable");
        }
    }

    /**
     * 获取ResultSet
     *
     * @param ps     PreparedStatement
     * @param params 参数
     * @return ResultSet result set
     */
    private static ResultSet getResultSet(PreparedStatement ps, List<Object> params) {
        ResultSet resultSet = null;
        try {
            for (int i = 0; i < params.size(); i++) {
                Object value = params.get(i);
                ps.setObject((i + 1), value);
            }
            return ps.executeQuery();
        } catch (Exception e) {
            throw new UnsupportedOperationException(e.getMessage(), e);
        } finally {
            close(resultSet);
        }
    }

    /**
     * 获取ResultSet
     *
     * @param ps     PreparedStatement
     * @param params 参数
     * @return ResultSet result set
     */
    private static long getCount(PreparedStatement ps, List<Object> params) {
        ResultSet resultSet = null;
        try {
            for (int i = 0; i < params.size(); i++) {
                Object value = params.get(i);
                ps.setObject((i + 1), value);
            }
            resultSet = ps.executeQuery();
            return resultSet.getInt("COUNT");
        } catch (Exception e) {
            throw new UnsupportedOperationException(e.getMessage(), e);
        } finally {
            close(resultSet);
        }
    }


    /**
     * 通过动态sql 实现复杂查询并映射
     *
     * @param <T>              泛型
     * @param dataSource       数据源
     * @param queryPojo        查询条件封装
     * @param paramTransformer 参数转换器
     * @param valueTransformer the value transformer
     * @return list数据 list
     */
    public static <T> List<T> queryForListBySql(DataSource dataSource, Object queryPojo,
                                                ParamTransformer paramTransformer, ValueTransformer valueTransformer) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;

        Class<T> clazz = JpaUtils.getResultClass(queryPojo);
        try {
            StringBuilder sql = new StringBuilder();
            connection = dataSource.getConnection();
            List<Object> params = new ArrayList<>();
            buidSql(queryPojo, null, sql, new StringBuilder(), params, paramTransformer);

            doLog(params, sql.toString());

            ps = connection.prepareStatement(sql.toString());
            resultSet = getResultSet(ps, params);

            List<T> dataList = new ArrayList<>();
            analysisResultSet(resultSet, clazz, dataList, valueTransformer);
            JpaUtils.THREAD_LOCAL.remove();
            return dataList;
        } catch (Exception e) {
            throw new UnsupportedOperationException(e.getMessage(), e);
        } finally {
            close(resultSet);
            close(ps);
            close(connection);
        }
    }

    /**
     * 通过动态sql 实现复杂查询并分页
     *
     * @param <T>              泛型
     * @param dataSource       数据源
     * @param queryPojo        查询条件封装
     * @param pageable         分页条件
     * @param paramTransformer 参数转换器
     * @param valueTransformer 值转换器
     * @return list数据 page
     */
    static <T> Page<T> queryForPageBySql(DataSource dataSource, Object queryPojo, Pageable pageable,
                                         ParamTransformer paramTransformer, ValueTransformer valueTransformer) {
        Connection connection = null;

        PreparedStatement queryPs = null;
        PreparedStatement countPs = null;
        ResultSet resultSet = null;

        Class<T> resultClass = JpaUtils.getResultClass(queryPojo);

        try {

            connection = dataSource.getConnection();
            StringBuilder querySql = new StringBuilder();
            StringBuilder countSql = new StringBuilder();

            List<Object> params = new ArrayList<>();
            justBuild( params, querySql, new StringBuilder(), queryPojo, pageable,
					paramTransformer);


            doLog(params, querySql.toString());
            doLog(params, countSql.toString());

            queryPs = connection.prepareStatement(querySql.toString());
            countPs = connection.prepareStatement(countSql.toString());
            long total = getCount(countPs, params);
            resultSet = getResultSet(queryPs, params);
            List<T> dataList = new ArrayList<>();
            analysisResultSet(resultSet, resultClass, dataList, valueTransformer);

            return new PageImpl<>(dataList, pageable, total);
        } catch (Exception e) {
            throw new UnsupportedOperationException(e.getMessage(), e);
        } finally {
            close(resultSet);
            close(countPs);
            close(queryPs);
            close(connection);
        }

    }

    /**
     * 解析ResultSet
     *
     * @param <T>              泛型
     * @param resultSet        resultSet
     * @param clazz            目标实体对象class
     * @param data             数据封装对象 可能是list 也可能是 目标实体对象class的实例
     * @param valueTransformer 值转换器
     */
    @SuppressWarnings("unchecked")
    private static <T> void analysisResultSet(ResultSet resultSet, Class<T> clazz, Object data,
                                              ValueTransformer valueTransformer) {
        try {

            List<Field> fields = JpaUtils.getDeclaredFields(clazz);
            Map<String, Field> fieldMap = new HashMap<>(10);
            cacheClassFields(fields, fieldMap);
            while (resultSet.next()) {
                T t;

                if (!(data instanceof List)) {
                    t = (T) data;
                } else {
                    t = clazz.newInstance();
                }

                setFieldValue(resultSet, valueTransformer, fieldMap, t);
                if (data instanceof List) {
                    ((List<T>) data).add(t);
                }

            }

        } catch (Exception e) {
            throw new UnsupportedOperationException(e.getMessage(), e);
        }
    }

    /**
     * 遍历时缓存属性 避免多次调用反射获取对象的成员变量
     *
     * @param fields   成员变量
     * @param fieldMap 成员变量map
     */
    private static void cacheClassFields(List<Field> fields, Map<String, Field> fieldMap) {
        for (Field field : fields) {
            String column;
            JpaSqlMapping mapping = field.getDeclaredAnnotation(JpaSqlMapping.class);
            if (Objects.nonNull(mapping)) {
                if (mapping.skip()) {
                    continue;
                }
                column = mapping.column();
                if (StringUtils.isBlank(column)) {
                    column = JpaUtils.humpToUnderline(field.getName()).toUpperCase();
                }

            } else {
                column = JpaUtils.humpToUnderline(field.getName()).toUpperCase();
            }
            fieldMap.put(column, field);

        }
    }

    /**
     * 设置成员变量的值
     *
     * @param <T>              泛型
     * @param resultSet        ResultSet
     * @param valueTransformer 值转换器
     * @param fieldMap         成员变量map
     * @param t                对象
     */
    private static <T> void setFieldValue(ResultSet resultSet, ValueTransformer valueTransformer,
                                          Map<String, Field> fieldMap, T t) {
        try {
            ResultSetMetaData metaData = resultSet.getMetaData();

            int count = metaData.getColumnCount();

            List<String> columns = new ArrayList<>();

            for (int i = 1; i <= count; i++) {
                columns.add(metaData.getColumnName(i).toUpperCase());
            }

            for (String column : columns) {
                if (!fieldMap.containsKey(column)) {
                    continue;
                }
                Object value = resultSet.getObject(column);
                if (Objects.isNull(value)) {
                    continue;
                }
                Field field = fieldMap.get(column);
                value = valueTransformer.transform(column, field.getType(), value);

                JpaUtils.setFieldValue(t, field, value);
            }

        } catch (Exception e) {
            throw new UnsupportedOperationException(e.getMessage(), e);
        }
    }

    /**
     * 记录带参数的日志
     *
     * @param params 参数
     * @param sql    sql语句
     */
    private static void doLog(List<Object> params, String sql) {
		int matches = StringUtils.countMatches(sql, "?");
		for (int i = 0; i < matches; i++) {
		    Object value = params.get(i);
		    if (value instanceof String) {
		        value = "'" + value + "'";
            }
			sql = StringUtils.replaceOnce(sql, "?", value.toString());
		}
		LOGGER.info(sql);
	}


    /**
     * 通过动态sql 实现复杂查询并分页
     *
     * @param params           the params
     * @param querySql         查询语句
     * @param countSql         查询语句
     * @param queryPojo        查询条件封装
     * @param pageable         分页条件
     * @param paramTransformer 参数转换器
     */
    static void justBuild(List<Object> params, StringBuilder querySql,
						  StringBuilder countSql, Object queryPojo,
						  Pageable pageable, ParamTransformer paramTransformer) {

		buidSql(queryPojo, pageable, querySql, countSql, params, paramTransformer);

		doLog(params, querySql.toString());
		doLog(params, countSql.toString());
	}


}
